﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DynORM.Enums;

namespace DynORM.TypeMappingSubsystem
{
    public class Types
    {
        private static Types instance_mssql;
        private static Types instance_postgresql;
        private static Types instance_mysql;
        private static Types instance_oracle;

        public static Types GetInstance(DBMSTypes type)
        {
            if (type == DBMSTypes.MSSQL)
            {
                if (instance_mssql == null)
                    instance_mssql = new Types(type);
                return instance_mssql;
            }
            else if (type == DBMSTypes.PostgreSQL)
            {
                if (instance_postgresql == null)
                    instance_postgresql = new Types(type);
                return instance_postgresql;
            }
            else if (type == DBMSTypes.MySQL)
            {
                if (instance_mysql == null)
                    instance_mysql = new Types(type);
                return instance_mysql;
            }
            else if (type == DBMSTypes.Oracle)
            {
                if (instance_oracle == null)
                    instance_oracle = new Types(type);
                return instance_oracle;
            }
            else
                throw new Exception("DBMS type is not supported.");
        }

        private List<TypeMapping> typeMappings = new List<TypeMapping>();
        private List<TypeMapping> reverseTypeMappings = new List<TypeMapping>();

        private Types(DBMSTypes type)
        {
            if (type == DBMSTypes.MSSQL)
            {
                //http://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx
                TypeMapping varbinary = new TypeMapping("String", "varbinary");
                reverseTypeMappings.Add(varbinary);
                TypeMapping xml = new TypeMapping("String", "xml");
                reverseTypeMappings.Add(xml);
                TypeMapping strings = new TypeMapping("String", "varchar");
                reverseTypeMappings.Add(strings);

                TypeMapping datetime = new TypeMapping("DateTime", "DateTime");
                typeMappings.Add(datetime);
                reverseTypeMappings.Add(datetime);
                TypeMapping ids = new TypeMapping("Guid", "uniqueidentifier");
                typeMappings.Add(ids);
                reverseTypeMappings.Add(ids);
                TypeMapping ints = new TypeMapping("Int32", "int");
                typeMappings.Add(ints);
                reverseTypeMappings.Add(ints);
                TypeMapping ints2 = new TypeMapping("Int16", "smallint");
                typeMappings.Add(ints2);
                reverseTypeMappings.Add(ints2);
                TypeMapping ints3 = new TypeMapping("Int64", "bigint");
                typeMappings.Add(ints3);
                reverseTypeMappings.Add(ints3);
                TypeMapping strings2 = new TypeMapping("String", "nvarchar");
                typeMappings.Add(strings2);
                reverseTypeMappings.Add(strings2);
                TypeMapping bools = new TypeMapping("Boolean", "bit");
                typeMappings.Add(bools);
                reverseTypeMappings.Add(bools);
                TypeMapping doubles = new TypeMapping("Decimal", "numeric");
                typeMappings.Add(doubles);
                reverseTypeMappings.Add(doubles);
                TypeMapping floats = new TypeMapping("Double", "float");
                typeMappings.Add(floats);
                reverseTypeMappings.Add(floats);
                TypeMapping singles = new TypeMapping("Single", "real");
                typeMappings.Add(singles);
                reverseTypeMappings.Add(singles);
            }
            else if (type == DBMSTypes.PostgreSQL)
            {
                //mapping taken from Npgsql docs (available in [folder where installed Npgsql]/PostgreSQL/Npgsql/docs/UserManual.html)
                TypeMapping strings = new TypeMapping("String", "character varying");
                reverseTypeMappings.Add(strings);
                TypeMapping integer = new TypeMapping("Int32", "integer");
                reverseTypeMappings.Add(integer);
                TypeMapping floats2 = new TypeMapping("Double", "double precision");
                reverseTypeMappings.Add(floats2);
                TypeMapping singles2 = new TypeMapping("Single", "real");
                reverseTypeMappings.Add(singles2);
                TypeMapping ints22 = new TypeMapping("Int16", "smallint");
                reverseTypeMappings.Add(ints22);
                TypeMapping ints32 = new TypeMapping("Int64", "bigint");
                reverseTypeMappings.Add(ints32);

                TypeMapping datetime = new TypeMapping("DateTime", "date");
                typeMappings.Add(datetime);
                reverseTypeMappings.Add(datetime);
                TypeMapping ids = new TypeMapping("Guid", "uuid");
                typeMappings.Add(ids);
                reverseTypeMappings.Add(ids);
                TypeMapping ints = new TypeMapping("Int32", "int4");
                typeMappings.Add(ints);
                reverseTypeMappings.Add(ints);
                TypeMapping ints2 = new TypeMapping("Int16", "int2");
                typeMappings.Add(ints2);
                reverseTypeMappings.Add(ints2);
                TypeMapping ints3 = new TypeMapping("Int64", "int8");
                typeMappings.Add(ints3);
                reverseTypeMappings.Add(ints3);
                TypeMapping strings2 = new TypeMapping("String", "varchar");
                typeMappings.Add(strings2);
                reverseTypeMappings.Add(strings2);
                TypeMapping bools = new TypeMapping("Boolean", "boolean");
                typeMappings.Add(bools);
                reverseTypeMappings.Add(bools);
                TypeMapping doubles = new TypeMapping("Decimal", "numeric");
                typeMappings.Add(doubles);
                reverseTypeMappings.Add(doubles);
                TypeMapping floats = new TypeMapping("Double", "float8");
                typeMappings.Add(floats);
                reverseTypeMappings.Add(floats);
                TypeMapping singles = new TypeMapping("Single", "float4");
                typeMappings.Add(singles);
                reverseTypeMappings.Add(singles);
            }
            else if (type == DBMSTypes.MySQL)
            {
                //there is no good docs for type mapping in Connector .NET between CLR and MySQL
                //mapping created based on this definition http://dev.mysql.com/doc/connector-net/en/connector-net-ref-mysqlclient-mysqlcommandmembers.html#connector-net-ref-mysqlclient-mysqldbtype
                TypeMapping binary = new TypeMapping("Guid", "binary");
                reverseTypeMappings.Add(binary);
                TypeMapping charc = new TypeMapping("String", "char");
                reverseTypeMappings.Add(charc);
                TypeMapping tinyint = new TypeMapping("Boolean", "tinyint");
                reverseTypeMappings.Add(tinyint);
                TypeMapping mediumint = new TypeMapping("Int32", "mediumint");
                reverseTypeMappings.Add(mediumint);
                TypeMapping enumstr = new TypeMapping("String", "enum");
                reverseTypeMappings.Add(enumstr);
                TypeMapping doubles2 = new TypeMapping("Decimal", "decimal");
                reverseTypeMappings.Add(doubles2);

                TypeMapping datetime = new TypeMapping("DateTime", "date");
                typeMappings.Add(datetime);
                reverseTypeMappings.Add(datetime);
                TypeMapping ids = new TypeMapping("Guid", "binary(16)");
                typeMappings.Add(ids);
                reverseTypeMappings.Add(ids);
                TypeMapping ints = new TypeMapping("Int32", "int");
                typeMappings.Add(ints);
                reverseTypeMappings.Add(ints);
                TypeMapping ints2 = new TypeMapping("Int16", "smallint");
                typeMappings.Add(ints2);
                reverseTypeMappings.Add(ints2);
                TypeMapping ints3 = new TypeMapping("Int64", "bigint");
                typeMappings.Add(ints3);
                reverseTypeMappings.Add(ints3);
                TypeMapping strings2 = new TypeMapping("String", "varchar");
                typeMappings.Add(strings2);
                reverseTypeMappings.Add(strings2);
                TypeMapping bools = new TypeMapping("Boolean", "bool");
                typeMappings.Add(bools);
                reverseTypeMappings.Add(bools);
                TypeMapping doubles = new TypeMapping("Decimal", "numeric");
                typeMappings.Add(doubles);
                reverseTypeMappings.Add(doubles);
                TypeMapping floats = new TypeMapping("Double", "double");
                typeMappings.Add(floats);
                reverseTypeMappings.Add(floats);
                TypeMapping singles = new TypeMapping("Single", "float");
                typeMappings.Add(singles);
                reverseTypeMappings.Add(singles);
            }
            else if (type == DBMSTypes.Oracle)
            {
                //http://docs.oracle.com/database/121/CNCPT/tablecls.htm#GUID-A8F3420D-093C-449F-87E4-6C3DDFA8BCFF
                TypeMapping strings = new TypeMapping("Guid", "RAW");
                reverseTypeMappings.Add(strings);
                TypeMapping integer = new TypeMapping("Boolean", "CHAR");
                reverseTypeMappings.Add(integer);
                //TypeMapping floats2 = new TypeMapping("Double", "double precision");
                //reverseTypeMappings.Add(floats2);
                //TypeMapping singles2 = new TypeMapping("Single", "real");
                //reverseTypeMappings.Add(singles2);
                //TypeMapping ints22 = new TypeMapping("Int16", "smallint");
                //reverseTypeMappings.Add(ints22);
                //TypeMapping ints32 = new TypeMapping("Int64", "bigint");
                //reverseTypeMappings.Add(ints32);

                TypeMapping datetime = new TypeMapping("DateTime", "DATE");
                typeMappings.Add(datetime);
                reverseTypeMappings.Add(datetime);
                TypeMapping ids = new TypeMapping("Guid", "RAW(16)");
                typeMappings.Add(ids);
                reverseTypeMappings.Add(ids);
                TypeMapping ints = new TypeMapping("Int32", "NUMBER(9)"); //INTEGER This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)
                typeMappings.Add(ints);
                reverseTypeMappings.Add(ints);
                TypeMapping ints2 = new TypeMapping("Int16", "NUMBER(4)"); //SMALLINT
                typeMappings.Add(ints2);
                reverseTypeMappings.Add(ints2);
                TypeMapping ints3 = new TypeMapping("Int64", "NUMBER(18)"); //LONG
                typeMappings.Add(ints3);
                reverseTypeMappings.Add(ints3);
                TypeMapping strings2 = new TypeMapping("String", "NVARCHAR2");
                typeMappings.Add(strings2);
                reverseTypeMappings.Add(strings2);
                TypeMapping bools = new TypeMapping("Boolean", "CHAR(1)");
                typeMappings.Add(bools);
                reverseTypeMappings.Add(bools);
                TypeMapping doubles = new TypeMapping("Decimal", "NUMBER");
                typeMappings.Add(doubles);
                reverseTypeMappings.Add(doubles);
                TypeMapping floats = new TypeMapping("Double", "BINARY_DOUBLE");
                typeMappings.Add(floats);
                reverseTypeMappings.Add(floats);
                TypeMapping singles = new TypeMapping("Single", "BINARY_FLOAT");
                typeMappings.Add(singles);
                reverseTypeMappings.Add(singles);
            }
            else
                throw new Exception("DBMS type is not supported.");
        }

        public string CLRType2DBTypeName(Type clrType)
        {
            string clrTypeName = Nullable.GetUnderlyingType(clrType) == null ? clrType.Name : Nullable.GetUnderlyingType(clrType).Name;
            string dbmsTypeName = CLRTypeName2DBTypeName(clrTypeName);
            return dbmsTypeName;
        }

        public string CLRTypeName2DBTypeName(string clrTypeName)
        {
            string dbmsTypeName = this.typeMappings.First<TypeMapping>(tm => string.Compare(tm.CLRName, clrTypeName, StringComparison.OrdinalIgnoreCase) == 0).DBMSName;
            return dbmsTypeName;
        }

        public string DBTypeName2CLRTypeName(string dbType)
        {
            string clrTypeName = this.reverseTypeMappings.First<TypeMapping>(tm => String.Compare(tm.DBMSName, dbType, StringComparison.OrdinalIgnoreCase) == 0).CLRName;
            return clrTypeName;
        }

        public Type DBTypeName2CLRType(string dbType, bool isNullable)
        {
            Type clrType;
            string clrTypeName = DBTypeName2CLRTypeName(dbType);

            if (isNullable == false || String.Compare(clrTypeName, "String", StringComparison.OrdinalIgnoreCase) == 0)
                clrType = Type.GetType("System." + clrTypeName);
            else
                //clrType = Type.GetType("System.Nullable").MakeGenericType(Type.GetType("System." + clrTypeName));
                clrType = (typeof(System.Nullable<>)).MakeGenericType(Type.GetType("System." + clrTypeName));

            return clrType;
        }

        //auxiliary methods
        public static bool IsCLRTypeNullable(Type type)
        {
            return (type.Name == typeof(Nullable<>).Name || type == typeof(string));
        }

        public static Type GetCLRNullableType(Type type)
        {
            if (IsCLRTypeNullable(type) == false)
                return (typeof(System.Nullable<>)).MakeGenericType(type);
            else
                return type;
        }

        public static IEnumerable<Type> GetCLRLessGreaterTypes()
        {
            List<Type> result = new List<Type>();

            result.Add(typeof(Single));
            result.Add(typeof(Double));
            result.Add(typeof(Int16));
            result.Add(typeof(Int32));
            result.Add(typeof(Int64));
            result.Add(typeof(Single?));
            result.Add(typeof(Double?));
            result.Add(typeof(Int16?));
            result.Add(typeof(Int32?));
            result.Add(typeof(Int64?));

            result.Add(typeof(Decimal));
            result.Add(typeof(Decimal?));

            result.Add(typeof(DateTime));
            result.Add(typeof(DateTime?));

            return result;
        }

        public static object GetDefaultValue(Type type)
        {
            if (type == typeof(Int16)
                || type == typeof(Int32)
                || type == typeof(Int64)
                || type == typeof(Decimal)
                || type == typeof(double)
                || type == typeof(Single))
                return 0;

            if (type == typeof(bool))
                return false;

            if (type == typeof(DateTime))
                return DateTime.MinValue;

            if (type == typeof(Guid))
                return Guid.Empty;

            //if (type == typeof(string)
            //    || type == Types.GetCLRNullableType(typeof(Int16))
            //    || type == Types.GetCLRNullableType(typeof(Int32))
            //    || type == Types.GetCLRNullableType(typeof(Int64))
            //    || type == Types.GetCLRNullableType(typeof(Decimal))
            //    || type == Types.GetCLRNullableType(typeof(double))
            //    || type == Types.GetCLRNullableType(typeof(Single))
            //    || type == Types.GetCLRNullableType(typeof(bool))
            //    || type == Types.GetCLRNullableType(typeof(DateTime))
            //    || type == Types.GetCLRNullableType(typeof(Guid)))
            //    return null;
            return null;
        }

        public static IEnumerable<Type> GetCLRCompatibleTypes(Type type)
        {
            List<Type> result = new List<Type>();
            //including corresponding nullable types if type nullable
            //and self
            result.Add(type);

            bool isNullableStructure;

            if (IsCLRTypeNullable(type) && type != typeof(string))
            {
                type = type.GetGenericArguments().First<Type>();
                isNullableStructure = true;
            }
            else
                isNullableStructure = false;

            if (type == typeof(Int16))
            {
                if (isNullableStructure)
                {
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                    result.Add(typeof(Int32?));
                    result.Add(typeof(Int64?));
                }
                else
                {
                    result.Add(typeof(Single));
                    result.Add(typeof(Double));
                    result.Add(typeof(Int32));
                    result.Add(typeof(Int64));
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                    result.Add(typeof(Int16?));
                    result.Add(typeof(Int32?));
                    result.Add(typeof(Int64?));
                }
            }
            if (type == typeof(Int32))
            {
                if (isNullableStructure)
                {
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                    result.Add(typeof(Int64?));
                }
                else
                {
                    result.Add(typeof(Single));
                    result.Add(typeof(Double));
                    result.Add(typeof(Int64));
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                    result.Add(typeof(Int32?));
                    result.Add(typeof(Int64?));
                }
            }
            if (type == typeof(Int64))
            {
                if (isNullableStructure)
                {
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                }
                else
                {
                    result.Add(typeof(Single));
                    result.Add(typeof(Double));
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                    result.Add(typeof(Int64?));
                }
            }
            if (type == typeof(Single))
            {
                if (isNullableStructure)
                {
                    result.Add(typeof(Double?));
                }
                else
                {
                    result.Add(typeof(Double));
                    result.Add(typeof(Single?));
                    result.Add(typeof(Double?));
                }
            }
            if (type == typeof(Double) && (!isNullableStructure))
                result.Add(typeof(Double?));
            if (type == typeof(Decimal) && (!isNullableStructure))
                result.Add(typeof(Decimal?));
            if (type == typeof(DateTime) && (!isNullableStructure))
                result.Add(typeof(DateTime?));
            if (type == typeof(Guid) && (!isNullableStructure))
                result.Add(typeof(Guid?));
            if (type == typeof(Boolean) && (!isNullableStructure))
                result.Add(typeof(Boolean?));

            return result;
        }
    }
}
